﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;

namespace Sys.SqlServer.Smo.Gui
{
    public partial class SchemaAnalyzer : Form
    {
        public SchemaAnalyzer()
        {
            InitializeComponent();

            txtSchemaFile.Text = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "schema.xml");

            if (LicenseManager.UsageMode != LicenseUsageMode.Designtime)
                Builder.DatabaseChanged += delegate(object sender, EventArgs e) { LoadDatabaseObjects(); };
        }

        private void OnClose(object sender, EventArgs e)
        {
            Close();
        }

        private void OnLoadingObjects(object sender, EventArgs e)
        {
            LoadDatabaseObjects();
        }

        private void OnAddRemoveItems(object sender, EventArgs e)
        {
            ListBox from = null;
            ListBox to = null;

            #region Switch

            switch (((Button)sender).Name)
            {
                case "btnAddTables":
                    from = lstAvailableTables;
                    to = lstSelectedTables;
                    break;

                case "btnRemoveTables":
                    from = lstSelectedTables;
                    to = lstAvailableTables;
                    break;

                case "btnAddViews":
                    from = lstAvailableViews;
                    to = lstSelectedViews;
                    break;

                case "btnRemoveViews":
                    from = lstSelectedViews;
                    to = lstAvailableViews;
                    break;

                case "btnAddProcedures":
                    from = lstAvailableProcedures;
                    to = lstSelectedProcedures;
                    break;

                case "btnRemoveProcedures":
                    from = lstSelectedProcedures;
                    to = lstAvailableProcedures;
                    break;

                case "btnAddFunctions":
                    from = lstAvailableFunctions;
                    to = lstSelectedFunctions;
                    break;

                case "btnRemoreFunctions":
                    from = lstSelectedFunctions;
                    to = lstAvailableFunctions;
                    break;

                case "btnAddTriggers":
                    from = lstAvailableTriggers;
                    to = lstSelectedTriggers;
                    break;

                case "btnRemoveTriggers":
                    from = lstSelectedTriggers;
                    to = lstAvailableTriggers;
                    break;
            }

            #endregion

            List<object> items = new List<object>();

            foreach (object o in from.SelectedItems)
            {
                items.Add(o);
                to.Items.Add(o);
            }

            foreach (object o in items)
                from.Items.Remove(o);
        }

        private void OnExecute(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtSchemaFile.Text))
                OnSchemaBrowsed(this, new EventArgs());

            if (rbtnSerialize.Checked)
                Serialize();

            if (rbtnDeserialize.Checked)
                Deserialize();
        }

        private void OnSchemaBrowsed(object sender, EventArgs e)
        {            
            if (rbtnSerialize.Checked)
            {
                SaveFileDialog d = new SaveFileDialog();
                d.Filter = "XML Files|*.xml";
                d.AddExtension = true;
                d.CheckFileExists = false;
                d.CheckPathExists = false;

                if (d.ShowDialog() == DialogResult.OK)
                    txtSchemaFile.Text = d.FileName;
            }

            if (rbtnDeserialize.Checked)
            {
                OpenFileDialog d = new OpenFileDialog();
                d.Filter = "XML Files|*.xml";
                d.AddExtension = true;
                d.CheckFileExists = true;
                d.CheckPathExists = true;
                d.Multiselect = false;

                if (d.ShowDialog() == DialogResult.OK)
                {
                    txtSchemaFile.Text = d.FileName;

                    Script s = new Script();
                    s.Load(new FileInfo(txtSchemaFile.Text));

                    foreach (Scriptlet sl in s.Data)
                    {
                        if (sl.Type == ScriptType.Schema)
                        {
                            if (sl.Object == ScriptObject.Table)
                                lstAvailableTables.Items.Add(sl.Name);

                            if (sl.Object == ScriptObject.View)
                                lstAvailableViews.Items.Add(sl.Name);

                            if (sl.Object == ScriptObject.StoredProcedure)
                                lstAvailableProcedures.Items.Add(sl.Name);

                            if (sl.Object == ScriptObject.Function)
                                lstAvailableFunctions.Items.Add(sl.Name);

                            if (sl.Object == ScriptObject.Trigger)
                                lstAvailableTriggers.Items.Add(sl.Name);
                        }
                    }
                }
            }
        }

        private void LoadDatabaseObjects()
        {
            new Action(delegate()
            {
                using (SqlConnection c = new SqlConnection(Builder.ConnectionString.ToString()))
                {
                    try
                    {
                        Microsoft.SqlServer.Management.Smo.Server s = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(c));

                        Microsoft.SqlServer.Management.Smo.Database db = s.Databases[Builder.ConnectionString.Database];

                        lstSelectedTables.Items.Clear();
                        lstSelectedViews.Items.Clear();
                        lstSelectedProcedures.Items.Clear();
                        lstSelectedFunctions.Items.Clear();
                        lstSelectedTriggers.Items.Clear();

                        lstAvailableTables.Items.Clear();
                        foreach (Microsoft.SqlServer.Management.Smo.Table t in db.Tables)
                            lstAvailableTables.Items.Add(string.Format("{0}.{1}", t.Schema, t.Name));

                        lstAvailableViews.Items.Clear();
                        foreach (Microsoft.SqlServer.Management.Smo.View v in db.Views)
                            lstAvailableViews.Items.Add(string.Format("{0}.{1}", v.Schema, v.Name));

                        lstAvailableProcedures.Items.Clear();
                        foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure p in db.StoredProcedures)
                            lstAvailableProcedures.Items.Add(string.Format("{0}.{1}", p.Schema, p.Name));

                        lstAvailableFunctions.Items.Clear();
                        foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction f in db.UserDefinedFunctions)
                            lstAvailableFunctions.Items.Add(string.Format("{0}.{1}", f.Schema, f.Name));

                        lstAvailableTriggers.Items.Clear();
                        foreach (Microsoft.SqlServer.Management.Smo.Trigger r in db.Triggers)
                            lstAvailableTriggers.Items.Add(r.Name);
                    }
                    catch (Exception e)
                    {
                    }
                }
            }).BeginInvoke(delegate(IAsyncResult ar) { }, null);
        }

        private void Serialize()
        {
            SqlSerializer serializer = new SqlSerializer();

            List<string> lTables = new List<string>();

            foreach (object o in lstSelectedTables.Items)
                lTables.Add(o.ToString());

            List<string> lViews = new List<string>();

            foreach (object o in lstSelectedViews.Items)
                lViews.Add(o.ToString());

            List<string> lProcedures = new List<string>();

            foreach (object o in lstSelectedProcedures.Items)
                lProcedures.Add(o.ToString());

            List<string> lFunctions = new List<string>();

            foreach (object o in lstSelectedFunctions.Items)
                lFunctions.Add(o.ToString());

            List<string> lTriggers = new List<string>();

            foreach (object o in lstSelectedTriggers.Items)
                lTriggers.Add(o.ToString());

            Script s = serializer.SerializeSchema(Builder.ConnectionString, lTables.ToArray(), lViews.ToArray(), lProcedures.ToArray(), lFunctions.ToArray(), lTriggers.ToArray());

            if (s != null)
            {
                s.AuthoringTool = GetType().FullName;
                s.Save(new FileInfo(txtSchemaFile.Text));
            }
        }

        private void Deserialize()
        {
            Script s = new Script();
            s.Load(new FileInfo(txtSchemaFile.Text));

            SqlSerializer serializer = new SqlSerializer();
            serializer.DeserializeSchema(Builder.ConnectionString, s);
        }
    }
}